"""step5_3_create_d_dict.py

目的：
    读入 stable_serv_price.xlsx，在本地数据库创建 STABLE_SERV_PRICE 表

步骤概要：
    1. 读取 stable_serv_price.xlsx 文件
    2. 在本地数据库创建 STABLE_SERV_PRICE 表（如果有就先删除）
    3. 遍历不同医院，将数据写入 STABLE_SERV_PRICE 表

运行：
   python STEP5干净表/step5_3_create_d_dict.py
"""

from __future__ import annotations
import os
import sys
from pathlib import Path
from typing import List, Dict
import argparse
import pandas as pd
from sqlalchemy import text
from sqlalchemy.engine import Engine
import time

ROOT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
if ROOT_DIR not in sys.path:
    sys.path.append(ROOT_DIR)

from config import create_db_engine  # noqa: E402

# 元数据文件与环境变量键名常量
STABLE_SERV_PRICE_FILE = os.path.join(ROOT_DIR, 'env.xlsx')
STABLE_SERV_PRICE_SHEET = 'stable_serv_price'
HSP_LIST_JSON = os.path.join(ROOT_DIR, 'hspList.json')

# 时间函数
t0 = time.time()
def elapsed() -> str:
    """返回自脚本启动以来的耗时 (HH:MM:SS)。
    用于将原先的 [INFO]/[START]/[DONE] 等等级标签替换成实时耗时。
    """
    
    # 实际时间，yyy-mm-dd hh:mm:ss
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    
    # 已消耗时间 XX hours XX minutes XX.XX seconds
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"


# ------------------------------
#  读取配置与环境
# ------------------------------
def read_stable_file(file_path: str) -> pd.DataFrame:
    """
    读取 stable_serv_price.xlsx 文件，返回 DataFrame。
    """
    df = pd.read_excel(file_path, sheet_name=STABLE_SERV_PRICE_SHEET, dtype=str)
    return df

def read_hsp_list(json_path: str) -> List[Dict[str, str]]:
    """
    读取 hspList.json 文件，返回医院列表。
    """
    import json
    with open(json_path, 'r', encoding='utf-8') as f:
        hsp_list = json.load(f)
    return hsp_list

def table_exists(conn, table_name: str) -> bool:
    """检查指定表是否存在于当前用户模式。"""
    query = text("SELECT 1 FROM user_tables WHERE table_name = :tbl")
    result = conn.execute(query, {"tbl": table_name.upper()}).first()
    return result is not None

def fetch_scene_serv_price(engine: Engine, hsp_abbr: str) -> pd.DataFrame:
    # 查询表 scene_item_ext，获得里面的单价，并且做成df
    query = text("""
        SELECT ITEM_NAME, MAX(P) AS PRICE
        FROM scene_item_ext sie
        WHERE sie.HSP_ABBR = :hsp_abbr
          AND sie.item_type NOT IN ('西药费','中成药费','中药饮片费','卫生材料费')
        GROUP BY ITEM_NAME
    """)

    df = pd.read_sql(query, engine, params={"hsp_abbr": hsp_abbr})
    return df

# ------------------------------
#  创建 STABLE_SERV_PRICE 表
# ------------------------------
def create_stable_serv_price_table(engine: Engine, df: pd.DataFrame):
    """
    在本地数据库中创建 STABLE_SERV_PRICE 表，并将 df 数据写入该表。
    如果表已存在，则先删除再创建。
    """
    with engine.connect() as conn:
        table_name = 'STABLE_SERV_PRICE'
        if table_exists(conn, table_name):
            print(f"[{elapsed()}] 检测到 {table_name} 已存在，准备删除重建。")
            conn.execute(text(f"DROP TABLE {table_name}"))
            conn.commit()
        else:
            print(f"[{elapsed()}] 检测到 {table_name} 不存在，准备创建新表。")

        conn.execute(text("""
            CREATE TABLE STABLE_SERV_PRICE (
                HSP_ABBR VARCHAR2(50),
                ITEM_CODE VARCHAR2(1000),
                ITEM_NAME VARCHAR2(4000),
                PRICE NUMBER
            )
        """))
        conn.commit()

# ------------------------------
#  插入数据到 STABLE_SERV_PRICE 表
# ------------------------------
def insert_data_to_stable_serv_price(engine: Engine, stable_df: pd.DataFrame, hsp_info: str):
    """
    将 df 数据插入到 STABLE_SERV_PRICE 表中，针对指定的 hsp_abbr。
    """
    # 获得参数
    hsp_abbr, hsp_lv = hsp_info['hsp_abbr'], hsp_info['j_hsp_lv']
    hsp_name = hsp_info.get('hsp_name', '')

    # 根据该医院实际使用单价的数据来决定使用哪一列
    scene_price_df = fetch_scene_serv_price(engine, hsp_abbr)
    # 将 scene_price_df 的 ITEM_NAME 与 stable_df 的 name 列进行匹配
    merged_df = pd.merge(stable_df, scene_price_df, left_on='name', right_on='item_name', how='inner')
    # 分别比较 p3, p2, p1, p0 列，统计 scene_price_df 的 PRICE 与 p3, p2, p1, p0 列的匹配数量
    match_counts = {}
    for level in ['3', '2', '1', '0']:
        pCol = 'p' + level
        count = (merged_df['price'] == merged_df[pCol]).sum()
        match_counts[level] = count
    # 选择匹配数量最多的列作为 pColName
    pColName = 'p' + max(match_counts, key=match_counts.get)

    # 如果所有都不匹配，即所有都是0：
    if match_counts[max(match_counts, key=match_counts.get)] == 0:
        # 通过医院名称与医院等级判断
        if '服务中心' in hsp_name or '社区卫生中心' in hsp_name or '卫生院' in hsp_name:
            pColName = 'p0'
        elif hsp_lv in [0,1,2,3,'0','1','2','3']:
            pColName = 'p' + str(hsp_lv)
        else:
            pColName = 'p0'  # 默认使用 p0 列

    temp_df = stable_df[['code', 'name', pColName]].copy()
    temp_df = temp_df[temp_df[pColName].notna() & (temp_df[pColName] != '') & (temp_df[pColName] != 'nan')]
    
    # 转换列名
    temp_df.rename(columns={
        'code': 'ITEM_CODE',
        'name': 'ITEM_NAME',
        pColName: 'PRICE'
    }, inplace=True)
    
    # 添加 HSP_ABBR 列
    temp_df['HSP_ABBR'] = hsp_abbr
    
    # 插入数据
    temp_df = temp_df[['HSP_ABBR', 'ITEM_CODE', 'ITEM_NAME', 'PRICE']]
    temp_df.to_sql('stable_serv_price', con=engine, if_exists='append', index=False)

# ------------------------------
#  主流程
# ------------------------------
def main():
    # 创建数据库引擎
    engine = create_db_engine()
    
    # 读取 stable_serv_price.xlsx 文件
    read_stable_file_path = STABLE_SERV_PRICE_FILE
    stable_df = read_stable_file(read_stable_file_path)
    hsp_list = read_hsp_list(HSP_LIST_JSON)
    print(f"[{elapsed()}] Read stable_serv_price.xlsx file.")
    
    # 创建 STABLE_SERV_PRICE 表
    create_stable_serv_price_table(engine, read_stable_file(read_stable_file_path))
    print(f"[{elapsed()}] Created STABLE_SERV_PRICE table in local database.")
    
    # 遍历医院列表，插入数据
    for hsp_info in hsp_list:
        insert_data_to_stable_serv_price(engine, stable_df, hsp_info)
        print(f"[{elapsed()}] Inserted data for hospital {hsp_info['hsp_abbr']} into STABLE_SERV_PRICE table.")
    print(f"[{elapsed()}] All data inserted into STABLE_SERV_PRICE table.")
    
if __name__ == "__main__":
    main()    
    